package com.conversationboard.batch.jobs.stats;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.GregorianCalendar;

import org.joda.time.DateTime;
import org.quartz.Job;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;

import com.conversationboard.database.BatchQueries;
import com.conversationboard.database.Database;
import com.conversationboard.logger.LogMessageType;
import com.conversationboard.logger.Logger;
import com.conversationboard.model.Board;
import com.conversationboard.model.Boards;
import com.conversationboard.model.propertybag.PropertyBag;
import com.conversationboard.serialisation.XmlSerialiser;

public class Stats implements Job {

	private void mostPopularThreads(StatsOutput statsOutput) throws JobExecutionException {

		Connection connection = null;

		try {
			connection = Database.getConnection();

			PreparedStatement statement = connection.prepareStatement(BatchQueries.MOST_POPULAR_THREADS);

			DateTime today = new DateTime();
			DateTime oneWeekAgo = today.minusDays(7);

			statement.setDate(1, new Date(oneWeekAgo.toDate().getTime()));
			statement.execute();

			ResultSet resultSet = statement.getResultSet();

			while (resultSet.next()) {

				StatsOutput.MostPopularThread thread = new StatsOutput.MostPopularThread();

				thread.setBoardId(resultSet.getInt("boardid"));
				thread.setThreadId(resultSet.getInt("threadid"));
				thread.setTitle(resultSet.getString("title"));
				thread.setPosts(resultSet.getInt("posts"));

				Board board = Boards.getBoard(thread.getBoardId());

				if (!board.isPrivateBoard()) {
					statsOutput.getMostPopularThreads().add(thread);

				}
			}

			statement.close();

		} catch (SQLException e) {
			throw new JobExecutionException(e);
		} finally {
			try {
				Database.close(connection);
			} catch (SQLException e) {
				throw new JobExecutionException(e);
			}
		}

	}


	private void threadStarters(StatsOutput statsOutput) throws JobExecutionException {

		Connection connection = null;

		try {

			connection = Database.getConnection();

			PreparedStatement statement = connection.prepareStatement(BatchQueries.THREAD_STARTERS);
			statement.execute();

			ResultSet resultSet = statement.getResultSet();

			while (resultSet.next()) {

				String name = resultSet.getString("name");
				int numberOfThreads = resultSet.getInt("threads");

				StatsOutput.ThreadStarter threadStarter = new StatsOutput.ThreadStarter();
				threadStarter.setName(name);
				threadStarter.setThreads(numberOfThreads);

				statsOutput.getThreadStarters().add(threadStarter);
			}

			statement.close();

		} catch (SQLException e) {
			throw new JobExecutionException(e);
		} finally {
			try {
				Database.close(connection);
			} catch (SQLException e) {
				throw new JobExecutionException(e);
			}
		}

	}


	private void threadsPerBoard(StatsOutput statsOutput) throws JobExecutionException {

		Connection connection = null;

		try {

			connection = Database.getConnection();

			PreparedStatement statement = connection.prepareStatement(BatchQueries.THREADS_PER_BOARD);
			statement.execute();

			ResultSet resultSet = statement.getResultSet();

			while (resultSet.next()) {

				String boardName = resultSet.getString("name");
				int numberOfThreads = resultSet.getInt("threads");

				StatsOutput.StatsBoard board = new StatsOutput.StatsBoard();
				board.setBoardName(boardName);
				board.setThreads(numberOfThreads);

				statsOutput.getThreadsPerBoard().add(board);
			}

			statement.close();

		} catch (SQLException e) {
			throw new JobExecutionException(e);
		} finally {
			try {
				Database.close(connection);
			} catch (SQLException e) {
				throw new JobExecutionException(e);
			}
		}

	}


	private void topPostersThisMonth(StatsOutput statsOutput) throws JobExecutionException {

		Connection connection = null;

		try {

			connection = Database.getConnection();

			PreparedStatement statement = connection.prepareStatement(BatchQueries.TOP_POSTERS_THIS_MONTH);

			/*
			 * Between the first day of this month and the first day of next
			 * month
			 */

			Calendar start = new GregorianCalendar();
			Calendar end = new GregorianCalendar();
			start.set(Calendar.DAY_OF_MONTH, 1);
			end.set(Calendar.MONTH, start.get(Calendar.MONTH) + 1);
			end.set(Calendar.DAY_OF_MONTH, 1);

			statement.setDate(1, new Date(start.getTimeInMillis()));
			statement.setDate(2, new Date(end.getTimeInMillis()));

			statement.execute();

			ResultSet resultSet = statement.getResultSet();

			while (resultSet.next()) {

				String name = resultSet.getString("displayname");
				int numberOfPosts = resultSet.getInt("posts");

				StatsOutput.Person person = new StatsOutput.Person();
				person.setName(name);
				person.setPosts(numberOfPosts);

				statsOutput.getTopPostersThisMonth().add(person);

			}

			statement.close();

		} catch (SQLException e) {
			throw new JobExecutionException(e);
		} finally {
			try {
				Database.close(connection);
			} catch (SQLException e) {
				throw new JobExecutionException(e);
			}
		}

	}


	private void topPostersOfAllTime(StatsOutput statsOutput) throws JobExecutionException {

		Connection connection = null;

		try {

			connection = Database.getConnection();

			PreparedStatement statement = connection.prepareStatement(BatchQueries.TOP_POSTERS_OF_ALL_TIME);
			statement.execute();

			ResultSet resultSet = statement.getResultSet();

			while (resultSet.next()) {

				String name = resultSet.getString("displayname");
				int numberOfPosts = resultSet.getInt("posts");

				StatsOutput.Person person = new StatsOutput.Person();
				person.setName(name);
				person.setPosts(numberOfPosts);

				statsOutput.getTopPostersOfAllTime().add(person);
			}

			statement.close();

		} catch (SQLException e) {
			throw new JobExecutionException(e);
		} finally {
			try {
				Database.close(connection);
			} catch (SQLException e) {
				throw new JobExecutionException(e);
			}
		}
	}


	private void postsPerMonth(StatsOutput statsOutput) throws JobExecutionException {

		Connection connection = null;

		try {

			connection = Database.getConnection();

			PreparedStatement statement = connection.prepareStatement(BatchQueries.POSTS_PER_MONTH);
			statement.execute();

			ResultSet resultSet = statement.getResultSet();

			while (resultSet.next()) {

				StatsOutput.PostsInMonth postsInMonth = new StatsOutput.PostsInMonth();
				postsInMonth.setYear(resultSet.getInt("year"));
				postsInMonth.setMonth(resultSet.getInt("month"));
				postsInMonth.setPosts(resultSet.getInt("posts"));

				statsOutput.getPostsPerMonth().add(postsInMonth);
			}

			statement.close();

		} catch (SQLException e) {
			throw new JobExecutionException(e);
		} finally {
			try {
				Database.close(connection);
			} catch (SQLException e) {
				throw new JobExecutionException(e);
			}
		}
	}

	
	private void postsPerWeek(StatsOutput statsOutput) throws JobExecutionException {

		Connection connection = null;

		try {

			connection = Database.getConnection();

			PreparedStatement statement = connection.prepareStatement(BatchQueries.POSTS_PER_WEEK);
			statement.execute();

			ResultSet resultSet = statement.getResultSet();

			while (resultSet.next()) {

				StatsOutput.PostsInWeek postsInWeek = new StatsOutput.PostsInWeek();
				postsInWeek.setWeek(resultSet.getString("week"));
				postsInWeek.setPosts(resultSet.getInt("posts"));

				statsOutput.getPostsPerWeek().add(postsInWeek);
			}

			statement.close();

		} catch (SQLException e) {
			throw new JobExecutionException(e);
		} finally {
			try {
				Database.close(connection);
			} catch (SQLException e) {
				throw new JobExecutionException(e);
			}
		}
	}


	private void stylesheetUsage(StatsOutput statsOutput) throws JobExecutionException {

		Connection connection = null;

		try {

			connection = Database.getConnection();

			PreparedStatement statement = connection.prepareStatement(BatchQueries.STYLESHEET_USAGE);
			statement.execute();

			ResultSet resultSet = statement.getResultSet();

			while (resultSet.next()) {

				StatsOutput.StyleSheetUsage usage = new StatsOutput.StyleSheetUsage();
				usage.setName(resultSet.getString("stylesheet"));
				usage.setUsage(resultSet.getInt("total"));

				statsOutput.getStylesheetUsage().add(usage);
			}

			statement.close();

		} catch (SQLException e) {
			throw new JobExecutionException(e);
		} finally {
			try {
				Database.close(connection);
			} catch (SQLException e) {
				throw new JobExecutionException(e);
			}
		}

	}


	public void execute(JobExecutionContext context) throws JobExecutionException {

		try {

			StatsOutput statsOutput = new StatsOutput();

			long startTime = System.currentTimeMillis();

			this.stylesheetUsage(statsOutput);
			this.threadsPerBoard(statsOutput);
			this.threadStarters(statsOutput);
			this.mostPopularThreads(statsOutput);
			this.topPostersOfAllTime(statsOutput);
			this.topPostersThisMonth(statsOutput);
			this.postsPerMonth(statsOutput);
			this.postsPerWeek(statsOutput);

			long elapsedTime = (System.currentTimeMillis() - startTime) / 1000;

			statsOutput.setSecondsToProcess(elapsedTime);

			XmlSerialiser<StatsOutput> serialiser = new XmlSerialiser<StatsOutput>();
			serialiser.setAlias("StartTime", java.util.Date.class);
			serialiser.setAlias("Stats", StatsOutput.class);
			serialiser.setAlias("PostsInMonth", StatsOutput.PostsInMonth.class);
			serialiser.setAlias("PostsInWeek", StatsOutput.PostsInWeek.class);
			serialiser.setAlias("Board", StatsOutput.StatsBoard.class);
			serialiser.setAlias("ThreadStarter", StatsOutput.ThreadStarter.class);
			serialiser.setAlias("Person", StatsOutput.Person.class);
			serialiser.setAlias("MostPopularThread", StatsOutput.MostPopularThread.class);
			serialiser.setAlias("StylesheetUsage", StatsOutput.StyleSheetUsage.class);
			String xml = serialiser.serialise(statsOutput);

			PropertyBag.put("stats", xml);

			long timeTaken = System.currentTimeMillis() - startTime;
			Logger.log(new java.util.Date(), "Statistics Job took: " + timeTaken + " milliseconds", null, LogMessageType.DEBUG);

		} catch (SQLException e) {
			throw new JobExecutionException(e);
		} finally {
		}

	}

}
